﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using Microsoft.Extensions.Logging;
using Sayook.Framework.DbCore.CreateSqlString;
using Sayook.Framework.DbCore.DbConnection;
using Sayook.Framework.DbCore.DbException;
using Sayook.Framework.DbCore.SqlModel;
using Sayook.Framework.Tools;

namespace Sayook.Framework.DbCore.DbContext
{
    /// <summary>
    /// 访问数据库基类
    /// </summary>
    /// <typeparam name="T">数据库模型</typeparam>
    public abstract class BaseDbContext<T> : DbContext where T : DbModelBase
    {
        #region Constractor&Property

        private SqlString _sqlString;

        /// <summary>
        /// 自定义参数模型
        /// </summary>
        private object CustomModel;

        /// <summary>
        /// 单个实体参数
        /// </summary>
        public T Entity { get; set; }

        /// <summary>
        /// 实体参数集合
        /// </summary>
        public IEnumerable<T> Entitys { get; set; }

        /// <summary>
        /// 清除属性
        /// </summary>
        private void ClearProperty()
        {
            Entity = null;
            Entitys = null;
            SqlString = null;
        }

        /// <summary>
        /// 选择Sql语句生成者
        /// </summary>
        /// <param name="connProvider">数据库连接</param>
        /// <returns></returns>
        private ICreateSql OptSqlBuilder(IDbConnectionProvider connProvider)
        {
            if (connProvider is MySqlDbConnection)
            {
                return CreateMysqlSql.SqlBuilder();
            }
            if (connProvider is SqlServerDbConnection)
            {
                return CreateSqlServerSql.SqlBuilder();
            }
            if (connProvider is OracleDbConnection)
            {
                return CreateOracleSql.SqlBuilder();
            }
            throw new CreateSqlException("暂不支持使用的数据库！");
        }

        /// <summary>
        /// 访问数据库
        /// </summary>
        /// <param name="connProvider">数据库连接</param>
        /// <param name="logger">日志</param>
        public BaseDbContext(IDbConnectionProvider connProvider, ILogger<BaseDbContext<T>> logger) : base(connProvider, logger)
        {
            SqlBuilder = OptSqlBuilder(connProvider);
        }

        /// <summary>
        /// Sql语句生成者
        /// </summary>
        public ICreateSql SqlBuilder { get; private set; }

        /// <summary>
        /// SQL语句对象
        /// </summary>
        public SqlString SqlString
        {
            get
            {
                if (_sqlString == null)
                {
                    _sqlString = new SqlString();
                }
                return _sqlString;
            }
            set
            {
                _sqlString = value;
            }
        }

        #endregion Constractor&Property

        #region SQL语句

        /// <summary>
        /// Execute语句
        /// </summary>
        private string ExecuteSql => CheckExecuteSql();

        /// <summary>
        /// 查询语句
        /// </summary>
        private string QuerySql => CheckQuerySql();

        /// <summary>
        /// 核验Execute语句
        /// </summary>
        /// <returns>SQL 语句</returns>
        private string CheckExecuteSql()
        {
            var sql = SqlString.Insert;
            if (string.IsNullOrWhiteSpace(sql))
            {
                sql = SqlString.UpdateSql();
                if (string.IsNullOrWhiteSpace(sql))
                {
                    sql = SqlString.DeleteSql();
                    if (string.IsNullOrWhiteSpace(sql))
                    {
                        throw new CreateSqlException("未确定操作命令！请调用 Insert()|Update()|Delete()");
                    }
                }
                if (string.IsNullOrWhiteSpace(SqlString.Where))
                {
                    throw new CreateSqlException("执行 UPDATE|DELETE 命令必须添加 WHERE 条件！请调用 Where()");
                }
                if (SqlString.Where?.IndexOf("@") > 0 && Parameter == null)
                {
                    throw new CreateSqlException("执行带参数操作务必传入参数！请调用 SetParameter()");
                }
            }
            else if (Parameter == null)
            {
                throw new CreateSqlException("执行 INSERT 命令务必传入参数！请调用 SetParameter()");
            }

            LogSqlOrder(sql, Parameter);
            return sql;
        }

        /// <summary>
        /// 核验查询语句
        /// </summary>
        /// <returns>SQL 语句</returns>
        private string CheckQuerySql()
        {
            if (string.IsNullOrWhiteSpace(SqlString.Select))
            {
                this.Select();
            }

            if (!string.IsNullOrWhiteSpace(SqlString.OrderBy) && string.IsNullOrWhiteSpace(SqlString.OrderBy))
            {
                throw new CreateSqlException("分页查询必须传入排序字段！请调用 OrderBy() | OrderByDesc()");
            }

            if (SqlString.Where?.IndexOf("@") > 0 && Parameter == null)
            {
                throw new CreateSqlException("带条件查询必须传入条件参数！请调用 SetParameter()");
            }
            var sql = SqlString.QuerySql();
            LogSqlOrder(sql, Parameter);
            return sql;
        }

        /// <summary>
        /// 记录 操作数据库 命令
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        private void LogSqlOrder(string sql, object parameter)
        {
            if (_logger == null) return;
            string parameterJson;
            if (Entity != null)
            {
                parameterJson = JsonHelper.ToJson(Entity);
            }
            else if (Entitys != null)
            {
                parameterJson = JsonHelper.ToJson(Entitys);
            }
            else if (CustomModel != null)
            {
                parameterJson = JsonHelper.ToJson(CustomModel);
            }
            else
            {
                parameterJson = "null";
            }
            _logger.LogInformation("Execute SQL:{0},Parameter:{1}", sql, parameterJson);
        }

        #endregion SQL语句

        #region Sql参数

        /// <summary>
        /// 请求参数
        /// </summary>
        private object Parameter => GetParameter();

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <returns></returns>
        private object GetParameter()
        {
            if (Entity != null)
            {
                return Entity;
            }
            if (Entitys != null)
            {
                return Entitys;
            }
            return CustomModel;
        }

        /// <summary>
        /// 设置参数
        /// </summary>
        /// <param name="entity">数据库实体参数</param>
        /// <returns></returns>
        public BaseDbContext<T> SetParameter(T entity)
        {
            Entity = entity;
            return this;
        }

        /// <summary>
        /// 设置参数
        /// </summary>
        /// <param name="entitys">数据库实体参数列表</param>
        /// <returns>BaseDbContext</returns>
        public BaseDbContext<T> SetParameter(IEnumerable<T> entitys)
        {
            Entitys = entitys;
            return this;
        }

        /// <summary>
        /// 设置自定义参数
        /// </summary>
        /// <param name="customModel">自定义参数</param>
        /// <returns>BaseDbContext</returns>
        public BaseDbContext<T> SetParameter<TCustom>(TCustom customModel) where TCustom : class
        {
            CustomModel = customModel;
            return this;
        }

        #endregion Sql参数

        #region Query 操作

        /// <summary>
        /// 返回单个对象
        /// </summary>
        /// <returns>单条数据</returns>
        public T FirstOrDefault()
        {
            var sql = QuerySql;
            try
            {
                using var dbConnection = _connProvider.Create(DbConnectionString);
                return dbConnection.Query<T>(sql, Parameter)?.FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw new ExecuteSqlException($"执行:FirstOrDefault,Sql:{sql},异常提示：{ex.Message}", ex);
            }
            finally
            {
                ClearProperty();
            }
        }

        /// <summary>
        /// 返回单个对象
        /// </summary>
        /// <returns>单条数据</returns>
        public async Task<T> FirstOrDefaultAsync()
        {
            var sql = QuerySql;
            try
            {
                using var dbConnection = _connProvider.Create(DbConnectionString);
                var result = await dbConnection.QueryAsync<T>(sql, Parameter);
                return result?.FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw new ExecuteSqlException($"执行:FirstOrDefault,Sql:{sql},异常提示：{ex.Message}", ex);
            }
            finally
            {
                ClearProperty();
            }
        }

        /// <summary>
        /// 返回第一行第一列
        /// </summary>
        /// <typeparam name="OutT">自定义类型</typeparam>
        /// <returns>单个数据</returns>
        public OutT Scalar<OutT>()
        {
            var sql = QuerySql;
            try
            {
                using var dbConnection = _connProvider.Create(DbConnectionString);
                return dbConnection.ExecuteScalar<OutT>(sql, Parameter);
            }
            catch (Exception ex)
            {
                throw new ExecuteSqlException($"执行:Scalar<OutT>,Sql:{sql},异常提示：{ex.Message}", ex);
            }
            finally
            {
                ClearProperty();
            }
        }

        /// <summary>
        /// 返回第一行第一列
        /// </summary>
        /// <typeparam name="OutT">自定义类型</typeparam>
        /// <returns>单个数据</returns>
        public async Task<OutT> ScalarAsync<OutT>()
        {
            var sql = QuerySql;
            try
            {
                using var dbConnection = _connProvider.Create(DbConnectionString);
                return await dbConnection.ExecuteScalarAsync<OutT>(sql, Parameter);
            }
            catch (Exception ex)
            {
                throw new ExecuteSqlException($"执行:Scalar<OutT>,Sql:{sql},异常提示：{ex.Message}", ex);
            }
            finally
            {
                ClearProperty();
            }
        }

        /// <summary>
        /// 返回第一行第一列
        /// </summary>
        /// <returns>单个数据</returns>
        public object Scalar()
        {
            var sql = QuerySql;
            try
            {
                using var dbConnection = _connProvider.Create(DbConnectionString);
                return dbConnection.ExecuteScalar(sql, Parameter);
            }
            catch (Exception ex)
            {
                throw new ExecuteSqlException($"执行:Scalar,Sql:{sql},异常提示：{ex.Message}", ex);
            }
            finally
            {
                ClearProperty();
            }
        }

        /// <summary>
        /// 返回第一行第一列
        /// </summary>
        /// <typeparam name="OutT">自定义类型</typeparam>
        /// <returns>单个数据</returns>
        public async Task<object> ScalarAsync()
        {
            var sql = QuerySql;
            try
            {
                using var dbConnection = _connProvider.Create(DbConnectionString);
                return await dbConnection.ExecuteScalarAsync(sql, Parameter);
            }
            catch (Exception ex)
            {
                throw new ExecuteSqlException($"执行:Scalar<OutT>,Sql:{sql},异常提示：{ex.Message}", ex);
            }
            finally
            {
                ClearProperty();
            }
        }

        /// <summary>
        /// 返回列表
        /// </summary>
        /// <returns>数据集</returns>
        public List<T> ToList()
        {
            var sql = QuerySql;
            try
            {
                using var dbConnection = _connProvider.Create(DbConnectionString);
                return dbConnection.Query<T>(sql, Parameter)?.AsList();
            }
            catch (Exception ex)
            {
                throw new ExecuteSqlException($"执行:ToList,Sql:{sql},异常提示：{ex.Message}", ex);
            }
            finally
            {
                ClearProperty();
            }
        }

        /// <summary>
        /// 返回列表
        /// </summary>
        /// <returns>数据集</returns>
        public async Task<List<T>> ToListAsync()
        {
            var sql = QuerySql;
            try
            {
                using var dbConnection = _connProvider.Create(DbConnectionString);
                var result = await dbConnection.QueryAsync<T>(sql, Parameter);
                return result?.AsList();
            }
            catch (Exception ex)
            {
                throw new ExecuteSqlException($"执行:ToList,Sql:{sql},异常提示：{ex.Message}", ex);
            }
            finally
            {
                ClearProperty();
            }
        }

        /// <summary>
        /// 返回自定义列表
        /// </summary>
        /// <typeparam name="Dto">自定义类型</typeparam>
        /// <returns>数据集</returns>
        public List<Dto> ToList<Dto>()
        {
            var sql = QuerySql;
            try
            {
                using var dbConnection = _connProvider.Create(DbConnectionString);
                return dbConnection.Query<Dto>(sql, Parameter)?.AsList();
            }
            catch (Exception ex)
            {
                throw new ExecuteSqlException($"执行:ToList<Dto>,Sql:{sql},异常提示：{ex.Message}", ex);
            }
            finally
            {
                ClearProperty();
            }
        }

        /// <summary>
        /// 返回列表
        /// </summary>
        /// <returns>数据集</returns>
        public async Task<List<Dto>> ToListAsync<Dto>()
        {
            var sql = QuerySql;
            try
            {
                using var dbConnection = _connProvider.Create(DbConnectionString);
                var result = await dbConnection.QueryAsync<Dto>(sql, Parameter);
                return result?.AsList();
            }
            catch (Exception ex)
            {
                throw new ExecuteSqlException($"执行:ToList,Sql:{sql},异常提示：{ex.Message}", ex);
            }
            finally
            {
                ClearProperty();
            }
        }

        #endregion Query 操作

        #region Modify 操作

        /// <summary>
        /// 执行原生SQL
        /// </summary>
        /// <param name="sqlRaw">原始语句</param>
        /// <returns>受影响行数</returns>
        public int ExeSqlRaw(string sqlRaw)
        {
            try
            {
                using var dbConnection = _connProvider.Create(DbConnectionString);
                return dbConnection.Execute(sqlRaw, Parameter);
            }
            catch (Exception ex)
            {
                throw new ExecuteSqlException($"执行:ExeSqlRaw,Sql:{sqlRaw},异常提示：{ex.Message}", ex);
            }
            finally
            {
                ClearProperty();
            }
        }

        /// <summary>
        /// 执行原生SQL
        /// </summary>
        /// <param name="sqlRaw">原始语句</param>
        /// <returns>受影响行数</returns>
        public async Task<int> ExeSqlRawAsync(string sqlRaw)
        {
            try
            {
                using var dbConnection = _connProvider.Create(DbConnectionString);
                return await dbConnection.ExecuteAsync(sqlRaw, Parameter);
            }
            catch (Exception ex)
            {
                throw new ExecuteSqlException($"执行:ExeSqlRaw,Sql:{sqlRaw},异常提示：{ex.Message}", ex);
            }
            finally
            {
                ClearProperty();
            }
        }

        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <returns>受影响行数</returns>
        public int Save()
        {
            var sql = ExecuteSql;
            try
            {
                using var dbConnection = _connProvider.Create(DbConnectionString);
                return dbConnection.Execute(sql, Parameter);
            }
            catch (Exception ex)
            {
                throw new ExecuteSqlException($"执行:Save,Sql:{sql},异常提示：{ex.Message}", ex);
            }
            finally
            {
                ClearProperty();
            }
        }

        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <returns>受影响行数</returns>
        public async Task<int> SaveAsync()
        {
            var sql = ExecuteSql;
            try
            {
                using var dbConnection = _connProvider.Create(DbConnectionString);
                return await dbConnection.ExecuteAsync(sql, Parameter);
            }
            catch (Exception ex)
            {
                throw new ExecuteSqlException($"执行:Save,Sql:{sql},异常提示：{ex.Message}", ex);
            }
            finally
            {
                ClearProperty();
            }
        }

        /// <summary>
        /// 保存并返回最新IDENTITY
        /// 仅支持自增ID
        /// </summary>
        /// <typeparam name="Tout">IDENTITY 类型</typeparam>
        /// <returns>IDENTITY</returns>
        public Tout SaveAndReturnId<Tout>()
        {
            if (string.IsNullOrWhiteSpace(SqlString.Insert))
            {
                throw new ExecuteSqlException($"SaveAndReturnId()仅用于新增操作！");
            }
            var sql = $"{ExecuteSql};SELECT @@IDENTITY";
            try
            {
                using var dbConnection = _connProvider.Create(DbConnectionString);
                return dbConnection.ExecuteScalar<Tout>(sql, Parameter);
            }
            catch (Exception ex)
            {
                throw new ExecuteSqlException($"执行:SaveAndReturnId,Sql:{sql},异常提示：{ex.Message}", ex);
            }
            finally
            {
                ClearProperty();
            }
        }

        /// <summary>
        /// 保存并返回最新IDENTITY
        /// 仅支持自增ID
        /// </summary>
        /// <typeparam name="Tout">IDENTITY 类型</typeparam>
        /// <returns>IDENTITY</returns>
        public async Task<Tout> SaveAndReturnIdAsync<Tout>()
        {
            if (string.IsNullOrWhiteSpace(SqlString.Insert))
            {
                throw new ExecuteSqlException($"SaveAndReturnId()仅用于新增操作！");
            }
            var sql = $"{ExecuteSql};SELECT @@IDENTITY";
            try
            {
                using var dbConnection = _connProvider.Create(DbConnectionString);
                return await dbConnection.ExecuteScalarAsync<Tout>(sql, Parameter);
            }
            catch (Exception ex)
            {
                throw new ExecuteSqlException($"执行:SaveAndReturnId,Sql:{sql},异常提示：{ex.Message}", ex);
            }
            finally
            {
                ClearProperty();
            }
        }

        #endregion Modify 操作

        #region Transation 操作

        /// <summary>
        /// 事务 执行原生SQL
        /// </summary>
        /// <param name="dbConnection">数据库连接</param>
        /// <param name="transaction">事务</param>
        /// <param name="sqlRaw">原生SQL</param>
        /// <returns>受影响行数</returns>
        public int TranExeSqlRaw(IDbConnection dbConnection, IDbTransaction transaction, string sqlRaw)
        {
            try
            {
                return dbConnection.Execute(sqlRaw, Parameter, transaction);
            }
            catch (Exception ex)
            {
                throw new ExecuteSqlException($"执行:TranExeSqlRaw,Sql:{sqlRaw},异常提示：{ex.Message}", ex);
            }
            finally
            {
                ClearProperty();
            }
        }

        /// <summary>
        /// 事务 执行SQL
        /// </summary>
        /// <param name="dbConnection">数据库连接</param>
        /// <param name="transaction">事务</param>
        /// <returns>受影响行数</returns>
        public int TranSave(IDbConnection dbConnection, IDbTransaction transaction)
        {
            var sql = ExecuteSql;
            try
            {
                var result = dbConnection.Execute(sql, Parameter, transaction);
                return result;
            }
            catch (Exception ex)
            {
                throw new ExecuteSqlException($"执行:TranSave,Sql:{sql},异常提示：{ex.Message}", ex);
            }
            finally
            {
                ClearProperty();
            }
        }

        #endregion Transation 操作
    }
}